package com.infore.statisticsAndAlarm.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.statisticsAndAlarm.entity.QuarterData;

@Repository
public interface IQuarterDataRepository
		extends JpaRepository<QuarterData, String>, JpaSpecificationExecutor<QuarterData> {

	List<QuarterData> findByStationCodeInAndFactorCodeInAndYearInAndQuarterInOrderByYearAscQuarterAsc(
			List<String> stationCode, List<String> factorCode, List<String> year, List<String> quarter);

	// 按照时间查询季度表均值信息
	@Query(value = "select * from QUARTER_DATA t where t.station_code in(:stationCodeList) and t.factor_code in(:factorCodeList) and concat(t.year,t.quarter) between :startTime and :endTime order by concat(t.year,t.quarter) asc", nativeQuery = true)
	List<QuarterData> findAvgInfo(@Param("stationCodeList") List<String> stationCodeList,
			@Param("factorCodeList") List<String> factorCodeList, @Param("startTime") String startTime,
			@Param("endTime") String endTime);
	
	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于多站自动站报表
	@Query(value = "select t.station_code as MN, t.factor_code as CODE , to_char( avg(t.data_value_avg),"
			+ "'fm999999990.999999999') as VALUE ,to_char(sum(t.real_num)) as COUNT "
			+ "from QUARTER_DATA t where (t.station_code in (:mnList)) and (t.factor_code in (:projectCodes)) "
			+ "and concat(t.year,t.quarter) between :startTime and :endTime "
			+ "group by t.station_code,t.factor_code order by t.station_code", nativeQuery = true)
	List<Map<String, Object>> audioMoreReport(@Param("startTime") String startTime, @Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);
	
	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于单点自动站报表
	@Query(value = "select t.station_code as MN,t.factor_code as CODE,concat(t.year,concat('-',t.quarter)) as TIME,t.data_value_avg as VALUE"
			+ " from QUARTER_DATA t where (t.station_code in (:mnList))"
			+ " and (t.factor_code in (:projectCodes)) "
			+ " and concat(t.year,t.quarter) between :startTime and :endTime "
			+ "order by concat(t.year,t.quarter) ", nativeQuery = true)
	List<Map<String, Object>> audioSingReport(@Param("startTime") String startTime, @Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);
}
